﻿namespace MyWebLog.Logic.QueryProviders
{
    using DatabaseAbstraction.Interfaces;
    using DatabaseAbstraction.Models;
    using System.Collections.Generic;
    using System.Data;

    /// <summary>
    /// This class contains queries to perform security-related activites.
    /// It uses the "security" query namespace.
    /// </summary>
    public sealed class SecurityQueryProvider : IDatabaseQueryProvider
    {
        /// <summary>
        /// The prefix used for queries from this provider
        /// </summary>
        public string Prefix
        {
            get { return _prefix; }
            set { _prefix = value; }
        }

        private string _prefix = "security.";

        #region Main

        /// <summary>
        /// Get the queries for security
        /// </summary>
        /// <param name="queries">
        /// The query library being built
        /// </param>
        public void Queries(IDictionary<string, DatabaseQuery> queries)
        {
            // Select
            queries.Add(Prefix + "login", Login());
            queries.Add(Prefix + "user.get", UserGet());
            queries.Add(Prefix + "permission.get", PermissionGet());

            // Insert

            // Update
            queries.Add(Prefix + "user.update.last_seen", UserUpdateLastSeen());

            // Delete
        }

        #endregion

        #region Select

        /// <summary>
        /// security.login
        /// </summary>
        /// <returns>
        /// A populated query
        /// </returns>
        private DatabaseQuery Login()
        {
            var query = new DatabaseQuery
            {
                SQL = @"SELECT
                    bu.blog_user_id, permission_id
                FROM blog_user bu
                    INNER JOIN blog_authorization ba ON bu.blog_user_id = ba.blog_user_id
                WHERE   email_address = @email_address
                    AND password_hash = @password_hash
                    AND blog_id       = @blog_id"
            };

            query.Parameters.Add("email_address", DbType.String);
            query.Parameters.Add("password_hash", DbType.String);
            query.Parameters.Add("blog_id", DbType.Int32);

            return query;
        }

        /// <summary>
        /// security.user.get
        /// </summary>
        /// <returns>
        /// A populated query
        /// </returns>
        private DatabaseQuery UserGet()
        {
            var query = new DatabaseQuery
            {
                SQL = @"SELECT
                    blog_user_id, status_id, email_address, first_name, last_name, display_name, created_date,
                    last_seen_date, verification_key
                FROM blog_user
                WHERE blog_user_id = @blog_user_id"
            };

            query.Parameters.Add("blog_user_id", DbType.Int32);

            return query;
        }

        /// <summary>
        /// security.permission.get
        /// </summary>
        /// <returns>
        /// A populated query
        /// </returns>
        private DatabaseQuery PermissionGet()
        {
            var query = new DatabaseQuery
            {
                SQL = @"SELECT
                    permission_id, description, level
                FROM permission
                WHERE permission_id = @permission_id"
            };

            query.Parameters.Add("permission_id", DbType.Int32);

            return query;
        }

        #endregion

        #region Update

        /// <summary>
        /// security.user.update.last_seen
        /// </summary>
        /// <returns>
        /// A populated query
        /// </returns>
        private DatabaseQuery UserUpdateLastSeen()
        {
            var query = new DatabaseQuery
            {
                SQL = @"UPDATE blog_user
                SET last_seen_date = @last_seen_date
                WHERE blog_user_id = @blog_user_id"
            };

            query.Parameters.Add("last_seen_date", DbType.DateTime);
            query.Parameters.Add("blog_user_id", DbType.Int32);

            return query;
        }

        #endregion
    }
}